package com.oocl.webmvc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.junit.Assert;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Repository;

import com.oocl.webmvc.dao.CustomerDao;
import com.oocl.webmvc.pojo.Customer;

/**
 * @description: 
 * @author  XIEMI2
 * @date Aug 8, 2017 6:48:29 PM
 * @version 1.0
 */
@Repository
public class CustomerDaoImpl implements CustomerDao{

	
	public static DataSource dataSource;
	
	public CustomerDaoImpl() {
		System.out.println("CustomerDaoImpl constructor....");
	}


	@Override
	public int addCustomer(Customer cust) {
		String sql = "insert into customers(id, name, sex, sal, birth) values(?, ?, ?, ?, ?)";
		Connection con = null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con = dataSource.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, cust.getId());
			pst.setString(2, cust.getName());
			pst.setBoolean(3, cust.isSex());
			pst.setDouble(4, cust.getSal());
			pst.setDate(5, new java.sql.Date(cust.getBirth().getTime()));
			m = pst.executeUpdate();
			con.close();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return m;
	}
	
	@Override
	public int delCustomer(String customerId) {
		String sql = "delete from customers where id = ?";
		Connection con = null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con = dataSource.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, customerId);
			m = pst.executeUpdate();
			con.commit();
			con.close();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return m;
	}


	@Override
	public int updateCustomer(Customer cust) {
		String sql = "update customers set name = ?, sex = ?, sal = ?, birth =? where id = ?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		int m = 0;
		try {
			con = dataSource.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, cust.getName());
			pst.setBoolean(2, cust.isSex());
			pst.setDouble(3, cust.getSal());
			pst.setDate(4, new java.sql.Date(cust.getBirth().getTime()));
			pst.setString(5, cust.getId());
			m = pst.executeUpdate();
			con.close();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return m;
	}

	@Override
	public List<Customer> findAllCustomers() {
		String sql = "select * from customers";
		List<Customer> customers = new ArrayList<Customer>();
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			con = dataSource.getConnection();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				String id = rs.getString("id");
				String name = rs.getString("name");
				boolean sex = rs.getBoolean("sex");
				double sal = rs.getDouble("sal");
				Date birth = new java.sql.Date(rs.getDate("birth").getTime());
				Customer menu = new Customer(id, name, sex, sal, birth);
				customers.add(menu);
			}
			con.close();
			pst.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return customers;
	}

}


